package cn.fintecher.pangolin.service.business.repository.jparepository;

import cn.fintecher.pangolin.entity.mysql.CaseInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Set;

public interface CaseInfoRepository extends JpaRepository<CaseInfo, String>, QuerydslPredicateExecutor<CaseInfo> {
    /**
     * 查询管理员委托方
     */
    @Query(value = "select DISTINCT a.principal_id,a.principal_name from case_info a left join user b on a.current_collector_id = b.id " +
            "left join department c on b.depart_id = c.id where c.depart_code like concat(:code,'%') and a.case_data_status != 'PAUSE' ", nativeQuery = true)
    List<Object[]> findPrincipalByManager(@Param("code") String code);

    /**
     * 查询催收员委托方
     */
    @Query(value = "select DISTINCT principal_id,principal_name from case_info where case_data_status != 'PAUSE' and current_collector_id = :id", nativeQuery = true)
    List<Object[]> findPrincipalByCollector(@Param("id") String id);

    void deleteByIdIn(String [] ids);

    @Query(value="select b.batchNumber,\n" +
                "    MAX(CASE b.case_type WHEN 'TEL_CASE' THEN b.count ELSE 0 END ) telNumber,\n" +
                "\t\tMAX(CASE b.case_type WHEN 'TEL_CASE' THEN b.totalAmount ELSE 0 END ) telTotalAmount,\n" +
                "    MAX(CASE b.case_type WHEN 'VISIT_CASE' THEN b.count ELSE 0 END ) visitNumber,\n" +
                "\t\tMAX(CASE b.case_type WHEN 'VISIT_CASE' THEN b.totalAmount ELSE 0 END ) visitTotalAmount\n" +
                "from \n" +
                "(select c.batch_number as batchNumber,sum(c.left_amt) as totalAmount,count(c.id) as count,c.case_type\n" +
                "\tfrom\n" +
                "(select id,batch_number,case_type,left_amt from case_info where department_id is not null) c  GROUP BY c.batch_number,c.case_type) b GROUP BY b.batchNumber",nativeQuery = true)
    List<Object[]> getOrgBatchNumberAndTotal();

    @Query(value="select b.batchNumber,\n" +
            "    MAX(CASE b.case_type WHEN 'TEL_CASE' THEN b.count ELSE 0 END ) telNumber,\n" +
            "\t\tMAX(CASE b.case_type WHEN 'TEL_CASE' THEN b.totalAmount ELSE 0 END ) telTotalAmount,\n" +
            "    MAX(CASE b.case_type WHEN 'VISIT_CASE' THEN b.count ELSE 0 END ) visitNumber,\n" +
            "\t\tMAX(CASE b.case_type WHEN 'VISIT_CASE' THEN b.totalAmount ELSE 0 END ) visitTotalAmount\n" +
            "from \n" +
            "(select c.batch_number as batchNumber,sum(c.left_amt) as totalAmount,count(c.id) as count,c.case_type\n" +
            "\tfrom\n" +
            "(select id,batch_number,case_type,left_amt from case_info where department_id is not null and principal_id in ?1) c  GROUP BY c.batch_number,c.case_type) b GROUP BY b.batchNumber",nativeQuery = true)
    List<Object[]> getOrgBatchNumberAndTotal(Set<String> principalIds);


    @Query(value="select b.batchNumber,\n" +
                "MAX(CASE b.case_type WHEN 'TEL_CASE' THEN b.count ELSE 0 END ) tel,\n" +
                "MAX(CASE b.case_type WHEN 'TEL_CASE' THEN b.totalAmount ELSE 0 END ) telTotalAmount,\n" +
                "MAX(CASE b.case_type WHEN 'VISIT_CASE' THEN b.count ELSE 0 END ) visit,\n" +
                "MAX(CASE b.case_type WHEN 'VISIT_CASE' THEN b.totalAmount ELSE 0 END ) bisitTotalAmount\n" +
                "from \n" +
                "(select c.batch_number as batchNumber,sum(c.left_amt) as totalAmount,count(c.id) as count,c.case_type\n" +
                "\tfrom\n" +
                "(select id,batch_number,case_type,left_amt from case_info where case_data_status =?1) c  GROUP BY c.batch_number,c.case_type) b GROUP BY b.batchNumber\n",nativeQuery = true)
    List<Object[]> getInCollectingBatchNumberAndTotal(String poolName);

    @Query(value="select b.batchNumber,\n" +
            "MAX(CASE b.case_type WHEN 'TEL_CASE' THEN b.count ELSE 0 END ) tel,\n" +
            "MAX(CASE b.case_type WHEN 'TEL_CASE' THEN b.totalAmount ELSE 0 END ) telTotalAmount,\n" +
            "MAX(CASE b.case_type WHEN 'VISIT_CASE' THEN b.count ELSE 0 END ) visit,\n" +
            "MAX(CASE b.case_type WHEN 'VISIT_CASE' THEN b.totalAmount ELSE 0 END ) bisitTotalAmount\n" +
            "from \n" +
            "(select c.batch_number as batchNumber,sum(c.left_amt) as totalAmount,count(c.id) as count,c.case_type\n" +
            "\tfrom\n" +
            "(select id,batch_number,case_type,left_amt from case_info where case_data_status =?1 and principal_id in ?2) c  GROUP BY c.batch_number,c.case_type) b GROUP BY b.batchNumber\n",nativeQuery = true)
    List<Object[]> getInCollectingBatchNumberAndTotal(String poolName,Set<String> principalIds);


    @Query(value="select batch_number from case_info where case_data_status =?1 GROUP BY batch_number ",nativeQuery = true)
    List<String> getBatchNumber(String poolName);

    @Query(value="select batch_number from case_info where case_data_status =?1 and principal_id in ?2 GROUP BY batch_number ",nativeQuery = true)
    List<String> getBatchNumber(String poolName,Set<String> principalIds);
}
